package org.ftc.dao;

import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.ftc.domain.Session;
import org.ftc.form.SessionForm;
import org.hibernate.Criteria;
import org.hibernate.EntityMode;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.engine.spi.TypedValue;
import org.hibernate.type.IntegerType;
import org.springframework.stereotype.Repository;

@Repository
public class SessionDao extends CurrentDao<Session, String> {

	@Override
	public Session load(String id) {
		return super.load(id);
	}

	@Override
	public Session get(String id) {
		return super.get(id);
	}

	@Override
	public synchronized boolean save(Session entity) {
		return super.save(entity);
	}

	@Override
	public synchronized boolean saveOrUpdate(Session entity) {
		return super.saveOrUpdate(entity);
	}

	@Override
	public synchronized boolean update(Session entity) {
		return super.update(entity);
	}

	@Override
	public synchronized boolean delete(Session entity) {
		return super.delete(entity);
	}

	@Override
	public synchronized boolean delete(String id) {
		return super.delete(id);
	}

	@Override
	public List<Session> findPage(int offset, int pageSize) {
		return super.findPage(offset, pageSize);
	}

	@Override
	public List<Session> findAll() {
		return super.findAll();
	}

	@Override
	public long countAll() {
		return super.countAll();
	}

	@SuppressWarnings("unchecked")
	public List<Session> findPage(SessionForm form, int offset, int pageSize) {
		try {
			Criteria criteria = getSession().createCriteria(Session.class);
			criteriaWsql(form, criteria);
			criteriaSort(form, criteria);
			criteria.addOrder(Order.desc("sessionNo"));
			criteria.setFirstResult(offset);
			criteria.setMaxResults(pageSize);
			List<Session> list = (List<Session>) criteria.list();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			getLog().error(e.toString());
			return null;
		}
	}

	public long count(SessionForm form) {
		try {
			Criteria criteria = getSession().createCriteria(Session.class);
			criteria.setProjection(Projections.rowCount());
			criteriaWsql(form, criteria);
			return (Long) criteria.uniqueResult();
		} catch (Exception e) {
			getLog().error(e.toString());
			return 0;
		}
	}

	@SuppressWarnings("unchecked")
	public Session findNext(Session session) {
		try {
			Criteria criteria = getSession().createCriteria(Session.class);
			criteria.add(Restrictions.or(
					Restrictions.gt("meetingDate", session.getMeetingDate()),
					Restrictions.and(Restrictions.eq("meetingDate", session.getMeetingDate()),
							Restrictions.gt("sessionNo", session.getSessionNo()))));

			List<Session> list = (List<Session>) criteria.list();
			return CollectionUtils.isEmpty(list) ? null : list.get(0);
		} catch (Exception e) {
			getLog().error(e.toString());
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	public List<Session> findSessions(Session session) {
		try {
			Criteria criteria = getSession().createCriteria(Session.class);
			criteria.add(Restrictions.ge("meetingDate", session.getMeetingDate()));
			criteria.setFirstResult(0);
			criteria.setMaxResults(5);
			List<Session> list = (List<Session>) criteria.list();
			Criteria criteria2 = getSession().createCriteria(Session.class);
			criteria2.add(Restrictions.lt("meetingDate", session.getMeetingDate()));
			criteria2.setFirstResult(0);
			criteria2.setMaxResults(5);
			List<Session> list2 = (List<Session>) criteria2.list();
			list.addAll(list2);
			return list;
		} catch (Exception e) {
			getLog().error(e.toString());
			return null;
		}
	}

	private void criteriaWsql(SessionForm form, Criteria criteria) {
		if (StringUtils.isNotBlank(form.getSessionNo())) {
			criteria.add(Restrictions.ilike("sessionNo", form.getSessionNo(), MatchMode.START));
		}
		if (StringUtils.isNotBlank(form.getSessionNo1()) && StringUtils.isNotBlank(form.getSessionNo2())) {
			criteria.add(new BetweenExpression("sessionNo", NumberUtils.toInt(form.getSessionNo1()), NumberUtils
					.toInt(form.getSessionNo2())));
		} else if (StringUtils.isNotBlank(form.getxNo1()) && StringUtils.isNotBlank(form.getxNo2())) {
			criteria.add(new BetweenExpression("sessionNo", NumberUtils.toInt("100" + form.getxNo1()),
					NumberUtils.toInt("100" + form.getxNo2())));
		}
		if (form.getMeetingDate1() != null && form.getMeetingDate2() != null) {
			criteria.add(Restrictions.between("meetingDate", form.getMeetingDate1(), form.getMeetingDate2()));
		}
		if (StringUtils.isNotBlank(form.getYear())) {
			criteria.add(Restrictions.eq("year", form.getYear()));
		}
		if (StringUtils.isNotBlank(form.getMonth())) {
			criteria.add(Restrictions.eq("month", form.getMonth()));
		}
		if (StringUtils.isNotBlank(form.getDay())) {
			criteria.add(Restrictions.eq("day", form.getDay()));
		}
	}

	@SuppressWarnings("serial")
	public class BetweenExpression implements Criterion {
		private final String propertyName;
		private final int value1;
		private final int value2;

		public BetweenExpression(final String propertyName, final int value1, final int value2) {
			this.propertyName = propertyName;
			this.value1 = value1;
			this.value2 = value2;
		}

		@Override
		public String toSqlString(final Criteria criteria, final CriteriaQuery criteriaQuery) throws HibernateException {
			Dialect dialect= criteriaQuery.getFactory().getDialect();
			final String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, this.propertyName);
			final String queryFragment = dialect instanceof MySQLDialect ? "cast(" + columns[0]
					+ " as DECIMAL(10,2)) between ? and ? " : "cast(" + columns[0] + " as int) between ? and ? ";
			return queryFragment;
		}

		@Override
		public TypedValue[] getTypedValues(final Criteria criteria, final CriteriaQuery criteriaQuery)
				throws HibernateException {
			// An ordered pair of a value and its Hibernate type
			return new TypedValue[] { new TypedValue(IntegerType.INSTANCE, Integer.valueOf(value1), EntityMode.POJO),
					new TypedValue(IntegerType.INSTANCE, Integer.valueOf(value2), EntityMode.POJO) };
		}
	}

}
